package com.decent.ejfadmin.finance.dao.provider;

import com.decent.ejfadmin.common.bean.Page;
import com.decent.ejfadmin.finance.request.SupplyCardBalanceParam;
import com.decent.ejfadmin.finance.request.SupplyCardDetailParam;
import com.decent.ejfadmin.utils.SQLWrapUtils;
import org.apache.commons.lang3.StringUtils;

import java.util.HashMap;

/**
 * 销卡
 *
 * @author zhangchao
 */
public class SupplyCardDaoProvider {
	private static final String PARAM_KEY = "param";

	public String querySupplyCardDetailCount(HashMap<String, Object> params) {
		StringBuffer sql = new StringBuffer(200);
		sql.append("select count(1) FROM  " + "  yuhuang_user_consumption yuc  "
				+ "LEFT JOIN yuhuang_supply_card ysc ON yuc.orderId = ysc.orderId  "
				+ "INNER JOIN yuhuang_sup_info ysi ON yuc.userId = ysi.user_id  "
				+ "INNER JOIN yuhuang_sup_user ysu ON yuc.userId = ysu.user_id ");

		SupplyCardDetailParam param = (SupplyCardDetailParam) params.get(PARAM_KEY);
		generateDetailQueryPageSql(sql, param);
		return sql.toString();
	}

	private void generateDetailQueryPageSql(StringBuffer sql, SupplyCardDetailParam param) {
		sql.append("where 1=1 ");

		if (null != param.getProductType()) {
			sql.append(" and ysc.productType = #{param.productType}");
		}
		if (null != param.getType()) {
			sql.append(" and yuc.consumeType = #{param.type}");
		}
		if (StringUtils.isNotBlank(param.getOrderId())) {
			sql.append(" and yuc.orderId = #{param.orderId}");
		}
		if (null != param.getStartTime()) {
			sql.append(" and yuc.startTime >= #{param.startTime}");
		}
		if (null != param.getEndTime()) {
			sql.append(" and yuc.startTime <= #{param.endTime}");
		}
		if (StringUtils.isNotBlank(param.getBatchId())) {
			sql.append(" and ysc.batchId = #{param.batchId}");
		}
		if (StringUtils.isNotBlank(param.getCardNumber())) {
			sql.append(" and ysc.cardNumber = #{param.cardNumber}");
		}
		if (StringUtils.isNotBlank(param.getUserAccount())) {
			sql.append(" and (instr(ysu.account ,#{param.userAccount})>0 or instr(ysi.name ,#{param.userAccount})>0)");
		}
		sql.append(" order by yuc.id desc");
	}

	public String querySupplyCardDetailPage(HashMap<String, Object> params) {
		StringBuffer sql = new StringBuffer(300);
		sql.append("SELECT  " + "  yuc.orderId,  " + "  yuc.channel,  " + "  IFNULL(ysc.batchId, '-') AS batchId,  "
				+ "  IFNULL(ysc.cardNumber, '-') AS cardNumber,  " + "  IFNULL(ysc.productType, 0) AS productType,  "
				+ "  ysu.account userAccount,  " + "  ysi. NAME userName,  " + "  DATE_FORMAT(  "
				+ "    yuc.startTime,  " + "    '%Y-%m-%d %H:%i:%S'  " + "  ) startTime,  " + "  yuc.consumeType,  "
				+ "  yuc.money,  " + "  yuc.balance,  " + "  ysc.productId,  " + "  ysc.face,  " + "  ysc.area,  "
				+ "  ysc.productType " + "FROM  " + "  yuhuang_user_consumption yuc  "
				+ "LEFT JOIN yuhuang_supply_card ysc ON yuc.orderId = ysc.orderId  "
				+ "INNER JOIN yuhuang_sup_info ysi ON yuc.userId = ysi.user_id  "
				+ "INNER JOIN yuhuang_sup_user ysu ON yuc.userId = ysu.user_id ");
		SupplyCardDetailParam param = (SupplyCardDetailParam) params.get(PARAM_KEY);
		generateDetailQueryPageSql(sql, param);
		Page page = param.getPageUnit();
		return SQLWrapUtils.wrapPage(sql, page.getBeginIndex(), page.getSize());
	}

	public String querySupplyCardBalanceCount(HashMap<String, Object> params) {
		StringBuffer sql = new StringBuffer(200);
		sql.append("select count(1) from yuhuang_supply_balance ysb "
				+ "inner join yuhuang_sup_info ysi on ysb.userId = ysi.user_id "
				+ "inner join yuhuang_sup_user ysu on ysi.user_id = ysu.user_id "
				+ "inner join yuhuang_sup_privilege ysp on ysp.user_id = ysu.user_id "
				+ "left join yuhuang_pm pm on pm.pm_id = ysu.pm_id ");
		SupplyCardBalanceParam param = (SupplyCardBalanceParam) params.get(PARAM_KEY);
		generateBalanceQueryPageSql(sql, param);
		return sql.toString();
	}

	private void generateBalanceQueryPageSql(StringBuffer sql, SupplyCardBalanceParam param) {
		sql.append("where 1=1 ");

		if (null != param.getStatus()) {
			sql.append("and ysu.status = #{param.status}");
		}
		if (null != param.getWithdrawStatus()) {
			sql.append("and ysp.withdraw_state = #{param.withdrawStatus}");
		}
		if (StringUtils.isNotBlank(param.getUserAccount())) {
			sql.append("and (instr(ysu.account ,#{param.userAccount})>0 or instr(ysi.name ,#{param.userAccount})>0) ");
		}
	}

	public String querySupplyCardBalancePage(HashMap<String, Object> params) {
		StringBuffer sql = new StringBuffer(300);
		sql.append("select ysu.account userAccount,ysi.name userName,ysu.status,"
				+ "ysp.withdraw_state withdrawStatus,pm.pm_name pmName,(ysb.balance-ysb.freezebalance) balance "
				+ "from yuhuang_supply_balance ysb " + "inner join yuhuang_sup_info ysi on ysb.userId = ysi.user_id "
				+ "inner join yuhuang_sup_user ysu on ysi.user_id = ysu.user_id "
				+ "inner join yuhuang_sup_privilege ysp on ysp.user_id = ysu.user_id "
				+ "left join yuhuang_pm pm on pm.pm_id = ysu.pm_id ");
		SupplyCardBalanceParam param = (SupplyCardBalanceParam) params.get(PARAM_KEY);
		generateBalanceQueryPageSql(sql, param);
		Page page = param.getPageUnit();
		return SQLWrapUtils.wrapPage(sql, page.getBeginIndex(), page.getSize());
	}

	public static void main(String[] args) {
		SupplyCardDaoProvider provider = new SupplyCardDaoProvider();
		HashMap<String, Object> params = new HashMap<String, Object>();
		SupplyCardDetailParam param = new SupplyCardDetailParam();
		params.put(PARAM_KEY, param);
		Page pageUnit = new Page<>(1, 10);
		param.setPageUnit(pageUnit);
		String querySupplyCardDetailPage = provider.querySupplyCardDetailPage(params);
		System.out.println(querySupplyCardDetailPage);
	}

}
